﻿<cfsilent>
	<cfscript>
		
		sql = "  SELECT a.institute_id,a.institute_name 
					FROM t_institute a
					WHERE a.institute_id = :depId ";
   		queryObj = new Query( datasource=application.dnsSlave ) ;
        queryObj.addParam( name="depId", value=event.getArg("DepID"), cfsqltype="cf_sql_varchar" );
        rs_department = queryObj.execute( sql=sql ).getResult();
		
		sql = "SELECT   a.sbj_id,
						a.sbj_name,
						a.sbj_short,
						a.sbj_english,
						b.institute_name,
						a.level_code,
						c.learning_branch,
						a.schooling_length,
						a.term_start,
						a.subject_code 
				FROM t_subject a 
					 INNER JOIN t_institute b ON b.institute_id = a.institute_id 
					 INNER JOIN t_learning_branch c ON c.b_id = a.b_id 
				WHERE a.institute_id = :Institute 
					  AND
					  a.sbj_direction = :SubjectDirection 
				ORDER BY a.level_code ASC,a.term_start ASC,a.sbj_name ASC ";
		queryObj = new Query( datasource=application.dnsSlave ); 
		queryObj.addParam( name="Institute", value=rs_department.institute_id, cfsqltype="cf_sql_varchar" );
		queryObj.addParam( name="SubjectDirection", value='0', cfsqltype="cf_sql_char" );
		rs_subject= queryObj.execute( sql=sql ).getResult();
		
		dictAdvice = getProperty("serviceFactory").getBean("senateDictionaryAdvice");
		
		temFile = GetTempDirectory() & createUUID() & ".xls";
		downFile = URLEncodedFormat(rs_department.institute_name & "专业设置", "utf-8");
		
		excel = getProperty("serviceFactory").getBean("spreadSheetObject");
	
		/* 创建工作簿 */
		spreadsheetObj = excel.SpreadSheetNew("专业信息", false);
		
		/* 添加表头 */
		excel.SpreadsheetAddrow(spreadsheetObj, "国家专业代码,专业,简称,英文,托管院系(部),培养层次,学科门类,学制,招生学期");
		
		row = 1;
		
		for ( a=1; a LTE rs_subject.recordCount; a++  ) {
			
			row++;
			
			/* 构建专业信息 */
			rowData = "" 
						& "," 
						& rs_subject["sbj_name"][a] 
						& "," 
						& rs_subject["sbj_short"][a]
						& "," 
						& rs_subject["sbj_english"][a]
						& "," 
						& rs_subject["institute_name"][a]
						& "," 
						& dictAdvice.getStudentLevel(rs_subject["level_code"][a])
						& "," 
						& rs_subject["learning_branch"][a]
						& "," 
						& numberFormat( rs_subject["schooling_length"][a], "_._")
						& "," 
						& dictAdvice.getTermEnrol(rs_subject["term_start"][a]);
			
			/* 写入专业信息 */
			excel.SpreadsheetAddrow(spreadsheetObj, rowData);
			
			/* 格式化 学制单元格 使用一位小数 */
			excel.SpreadsheetFormatCell(spreadsheetObj, {"dataformat"="0.0"}, row, 8);
			
			/* 将第一列格式设置为文本 */
			excel.SpreadsheetFormatCell(spreadsheetObj, {"dataformat"="@"}, row, 1);
			
			/* 将国家专业代码写入 */
			excel.SpreadsheetSetCellValue(spreadsheetObj, rs_subject["subject_code"][a], row, 1);
			
			/* 查找专业方向 */
			
			sql = "SELECT  a.sbj_name,
						   a.sbj_short,
						   a.sbj_english
					FROM t_subject a 
					WHERE a.sbj_id LIKE :parent 
					ORDER BY a.sbj_id ASC";
			queryObj = new Query( datasource=application.dnsSlave ); 
			queryObj.addParam( name="parent", value=rs_subject["sbj_id"][a] & '_', cfsqltype="cf_sql_varchar" );
			rs_dir= queryObj.execute( sql=sql ).getResult();
			
			/* 如果有专业方向 */
			for (b=1; b LTE rs_dir.recordCount; b++  ) {
				
				row++;
				
				/* 构建专业方向信息 */
				rowData = "" 
						& "," 
						& rs_dir["sbj_name"][b] 
						& "," 
						& rs_dir["sbj_short"][b]
						& "," 
						& rs_dir["sbj_english"][b]
						& "," 
						& ""
						& "," 
						& ""
						& "," 
						& ""
						& "," 
						& ""
						& "," 
						& "";
				
				/* 写入专业信息 */
				excel.SpreadsheetAddrow(spreadsheetObj, rowData);
				
			}
			
		}
		
		excel.SpreadSheetWrite(spreadsheetObj, temFile, true);
		
	</cfscript>

	<cfheader name="Content-Disposition" value="attachment; filename=#downFile#.xls" />
	<cfcontent file="#temFile#" reset="yes" type="application/msexcel" deletefile="yes" />

</cfsilent>